<html>
<head>
<style>
body {
	font-family: Serif;
	font-size: 12pt;
	margin-top: 3em;
	margin-bottom: 1em;
	margin-left: 5%;
	margin-right: 5%;
}

h1 {
	font-family: Tahoma, Sans-Serif;
	font-size: 19pt;
	font-weight: normal;
	color: #202020;
}

h2 {
	font-family: Tahoma, Sans-Serif;
	font-size: 15pt;
	font-weight: normal;
	color: #DE003C;
	background-color: #FFFFFF;
	margin-bottom: 1em;
	margin-top: 1.5em;
	border-bottom: 1px solid #658ED0;
	padding: 3px;
	padding-left: 0px;
}

h3 {
	font-family: Sans-Serif;
	font-size: 13pt;
	color: #202020;
	font-weight: bold;
	margin-bottom: 1em;
	margin-top: 1.5em;
}

pre {
	font-family: Courier New, Courier, monospace;
	font-size: 10pt;
	padding: 0.5em;
	margin-left: 1em;
	margin-right: 1em;
}

span.code {
	font-family: Courier New, Courier, monospace;
	font-size: 10pt;
    font-weight: bold;
}

span.hey {
    font-weight: bold;
	color: #DE003C;
}

div.box {
	border: solid;
	border-width: thin;
	border-color: black;
    padding: 0.5em;
	margin: 1em;
}
div.box-label {
	margin-left: 1em;
	position: relative;
	top: -1em;
}

hr {
	margin-top: 1em;
	margin-bottom: 1em;
}

a {
	text-decoration: none;
	color: #13F;
}

a:hover {
	color: #DE003C;
}

table {
	border-collapse: collapse;
	border: 1px solid gray;
	margin-left: 1em;
	margin-right: 1em;
	padding: 0;
}

td {
	border: 1px solid gray;
	padding: 3px;
	font-family: Sans-Serif;
	font-size: 10pt;
}
th {
	background-color: #eee;
	border: 1px solid gray;
	padding: 3px;
	font-family: Sans-Serif;
	font-size: 10pt;
}
</style>
</head>
<body>

<h1>SqlMapper</h1>

<h2>What is SqlMapper?</h2>

SqlMapper is a tool that generates source code based on annotated SQL table definitions.
<p>
SqlMapper is not intended to be a full ORM/DAO solution, but just a way to centralize
and automate your data definitions.  It is meant to be used as a 'preprocessor'
in conjunction with an ORM/DAO package such as <a href='http://code.google.com/p/persist/'>Persist</a>.
<p>
There are many ORM/DAO packages to relieve developers from writing the repetitive
code for storing and retrieving data from a database.  This is great, but most of
these packages still require you to define your data structures in more than
one place.  For example, you may initially define your database tables in a SQL file,
and then have to write separate configuration or mapping files defining how
the tables map to your model objects. Or you have to write your
model classes and make sure that they map to the tables in your schema.
Some packages try to use the database metadata to avoid this duplication
but the metadata support varies from one database to another making this
a fragile solution.
<p>
SqlMapper attempts to address these problems by allowing you to define your object
models at the same time and in the same place that you define your database schema.
You do this by adding annotation comments to your database table and column definitions.
SqlMapper then reads this annotated SQL schema file along with one or more template files,
and it outputs a source code file for each database table definition.
<p>
<img src='sqlmapper.png'>

<h2>Annotations<a name="annotations"></a></h2>

There are only two types of annotations: <b>@model</b> for identifying tables that
are to be mapped to model objects and <b>@field</b> for identifying columns that
are to be mapped to model fields. Each can take a number of optional arguments.
<p>
<table>
<tr>
<th colspan=2>@model(args)</th>
</tr>
<tr>
<td><b>name</b>=&lt;model_name&gt;</td>
<td>Overrides the default table to model name mapping defined in the config file.</td>
</tr>
<tr>
<td><b>pkg</b>=&lt;model_package&gt;</td>
<td>Overrides the config property defining the Java package the model will be placed in.</td>
</tr>
<tr>
<td><b>template</b>=&lt;template_name&gt;</td>
<td>Overrides the config property defining the default template to use to generate this model.</td>
</tr>
<tr>
<td><b>suffix</b>=&lt;string&gt;</td>
<td>Overrides the config property defining the default output model file suffix.</td>
</tr>
<tr>
<td><b>join</b></td>
<td>Attribute indicating that this is a join table (see: <b>hmab2</b> field property below)</td>
</tr>
<tr>
<th colspan=2>@field(args)</th>
</tr>
<tr>
<td><b>auto_inc</b></td>
<td>Attribute indicating that this is an auto-increment column</td>
</tr>
<tr>
<td><b>pk</b></td>
<td>Attribute indicating that this is a primary key column</td>
</tr>
<tr>
<td><b>not_mapped</b></td>
<td>Attribute indicating that this is column should not be mapped to a field in the model</td>
</tr>
<tr>
<td><b>type</b>=&lt;field_type&gt;</td>
<td>Specifies the field type, overriding the default column-to-field mappings defined in the config file</td>
</tr>
<tr>
<td><b>has1</b>=&lt;model_name&gt;</td>
<td>Specifies that this column is a foreign key and that this model
(the one being defined by this table) has a field which points to an
instance of the indicated model (which is identified by this foreign key).
<p>
<b>has1</b> tags are also used to identify the models in a join table (see <b>hmab2</b> below)
</td>
</tr>
<tr>
<td><b>has1to1</b>=&lt;model_name&gt;</td>
<td>Specifies that this column is a foreign key and that this model
(the one being defined by this table) and the one identified by this field
each have a field which points to an instance of the other.
</td>
</tr>
<tr>
<td><b>belongs2</b>=&lt;model_name&gt;</td>
<td>Specifies that this column is a foreign key and that the model
identified by this field contains a list of these models (the ones being
defined by this table).
</td>
</tr>
<tr>
<td><b>h1b2</b>=&lt;model_name&gt;</td>
<td>This is a shortcut for the combination of <b>has1</b> and <b>belongs2</b>
</td>
</tr>
<tr>
<td><b>hmab2</b>=&lt;model_name&gt;</td>
<td>This stands for 'has many and belongs to'.  It is used in join tables (ones
with the <b>join</b> model attribute).  Join tables are expected to have 2 foreign
keys with <b>has1</b> annotations indicating the model they each point to.
This will result is each model having a list of instances of the other model (ie.
a two way, many to many relation).
</td>
</tr>
</table>
<p>
Here is an example of an annotated schema
<div class='box'>
<pre>
-- @model(name=University)
CREATE TABLE schools (
    id INTEGER                      -- @field(pk,auto_inc)
       GENERATED BY DEFAULT AS IDENTITY(START WITH 100) PRIMARY KEY,
    name varchar(255) NOT NULL,     -- @field
    city varchar(255) NOT NULL,     -- @field
    state varchar(255) NOT NULL     -- @field
);

-- @model(name=Course)
CREATE TABLE courses (
    id INTEGER                      -- @field(pk,auto_inc)
       GENERATED BY DEFAULT AS IDENTITY(START WITH 100) PRIMARY KEY,
    name varchar(255) NOT NULL,     -- @field
    school_id int NOT NULL          -- @field(type=int,h1b2=University)
);

CREATE TABLE teachers (             -- @model(name=Professor)
    id INTEGER                      -- @field(pk,auto_inc)
       GENERATED BY DEFAULT AS IDENTITY(START WITH 100) PRIMARY KEY,
    name varchar(255) NOT NULL      -- @field(name=prof)
);

CREATE TABLE students (             -- @model
    id INTEGER                      -- @field(pk,auto_inc)
       GENERATED BY DEFAULT AS IDENTITY(START WITH 100) PRIMARY KEY,
    name varchar(255) NOT NULL,     -- @field
    advisor_id int,                 -- @field(type=int,has1=Professor)
    school_id int NOT NULL          -- @field(type=int,belongs2=University)
);

CREATE TABLE student_courses (      -- @model(join)
    student_id integer,             -- @field(has1=Student)
    course_id integer               -- @field(has1=Course)
);

-- @model
CREATE TABLE transcripts (          
    id INTEGER                      -- @field(pk,auto_inc)
       GENERATED BY DEFAULT AS IDENTITY(START WITH 100) PRIMARY KEY,
    student_id int NOT NULL,        -- @field(type=int,has1to1=Student)
    reg_date date NOT NULL,         -- @field
    grad_date date,                 -- @field
    gpa float NOT NULL,             -- @field
    secrets varchar(255),           -- @field(not_mapped)
    UNIQUE(student_id)
);
</pre>
</div>
<div class="box-label">
(test/hsql-test.sql)
</div>
<p>
The parser is rather simple and so you must follow a couple of rules
when annotating your table definitions:
<ol>
<li>@model annotations must be placed either on the same line or
the line directly preceding the 'create table' line.</li>
<li>@field annotations must be on the same line that the column
name and column type are defined on.</li>
</ol>

<h2>Templates<a name="templates"></a></h2>

The source code for your models is generated by taking the information
gleaned from each annotated table and applying it to a template file.
<p>
The template language is very simple but powerful.  It was originally
developed for the <a href="http://javaviewcontrol.sourceforge.net/">JVC (Java View Control) project</a>.
It defines the following 5 tags, but SqlMapper templates only really needs the first 2:
<p>
<table>
<tr><td nowrap valign=top><span class='code'>[[ ... ]]</span></td><td nowrap valign=top><b>Java-code-tag</b></td>
<td>These tags contain Java code which will be executed as part of the
model generation process.  This code has access to a <a href="#model">Model</a> 
object via the <span class='code'>model</span> variable.
</td></tr>
<tr><td nowrap valign=top><span class='code'>[[= ... ]]</span></td><td nowrap valign=top><b>Java-expression-tag</b></td>
<td>
These tags contain Java code which evaluates to a String.  They have access to the same
variables as the Java-code-tags.
</td></tr>
<tr><td nowrap valign=top><span class='code'>[[== ... ]]</span></td><td nowrap valign=top><b>HTML escaped Java-expression-tag</b></td>
<td>
These tags are exactly the same as Java-expression-tags except their output is HTML escaped. <span class='hey'>Not used by SqlMapper</span>
</td></tr>
<tr><td nowrap valign=top><span class='code'>[[+ ... ]]</span></td><td nowrap valign=top><b>Include-tag</b></td>
<td>
These tags contain a path to a file to be included.
begin with an underscore character</em> (ie. _myfile.jhtml) to distinguish it from
a regular view template. <span class='hey'>Not used by SqlMapper</span>
</td></tr>
<tr><td nowrap valign=top><span class='code'>[[! ... ]]</span></td><td nowrap valign=top><b>Comment-tag</b></td>
<td>
Anything inside of comment-tags is ignored (including other JVC tags).
</td></tr>
</table>
<p>
Here is an example template illustrating what information 
is available in the templates and how it can be accessed.
<p>
<div class='box'>
<pre>
Model Info:
    table name:    [[= model.tableName]] 
    class name:    [[= model.className]] 
    class package: [[= model.classPkg]] 
    template:      [[= model.template]] 
    file suffix:   [[= model.fileSuffix]] 
  fields:
[[ for (Field f : model.fields) { ]]
      field name:     [[= f.fieldName]] 
      column name:    [[= f.columnName]] 
      field type:     [[= f.fieldType]] 
      column type:    [[= f.columnType]] 
      primary key:    [[= f.primaryKey]] 
      auto increment: [[= f.autoIncrement]] 

[[}]]
</pre>
</div>
<div class="box-label">
(test/templates/ModelInfo.tpl)
</div>
<p>
...which when applied to this annotated schema:
<p>
<div class='box'>
<pre>
-- @model(template=ModelInfo.tpl, suffix=.txt)
CREATE TABLE bike_parts (
    id int primary key auto increment,  -- @field(pk,auto_inc)
    part_name varchar(255) NOT NULL,    -- @field
    in_stock int NOT NULL DEFAULT 0,    -- @field(name=inventory)
    price numeric(8,2) NOT NULL         -- @field
);
</pre>
</div>
<div class="box-label">
(test/info-test.sql)
</div>
<p>
...produces this output:
<p>
<div class='box'>
<pre>
Model Info:
    table name:    bike_parts 
    class name:    BikePart 
    class package:  
    template:      ModelInfo.tpl 
    file suffix:   .txt 
  fields:
      field name:     id 
      column name:    id 
      field type:     Integer 
      column type:    int 
      primary key:    true 
      auto increment: true 

      field name:     inventory 
      column name:    in_stock 
      field type:     Integer 
      column type:    int 
      primary key:    false 
      auto increment: false 

      field name:     partName 
      column name:    part_name 
      field type:     String 
      column type:    varchar(255) 
      primary key:    false 
      auto increment: false 

      field name:     price 
      column name:    price 
      field type:     Float 
      column type:    numeric(8,2) 
      primary key:    false 
      auto increment: false 
</pre>
</div>
<div class="box-label">
(test/txt/BikeParts.txt)
</div>
<p>
(You can use the ant <em>test-gen</em> target to generate this output too).
<p>
For a more realistic example check out the test/templates/PersistHsqlModel.java.tpl file.

<h2>Configuration<a name="configuration"></a></h2>

Part of the input to SqlMapper is a configuration file.  This is a Java properties file that
allows you to specify default behaviors and additional file and directory locations.
<p>
Here is an example configuration file (the same one that was used to generate the output
in the previous section):
<p>
<div class='box'>
<pre>
# SQLMapper config

# input parameters -----------------------------

sql.file = test/info-test.sql
template.dir = test/templates
template.suffix = .tpl
default.template = ModelInfo.tpl

# output parameters ------------------------------

out.dir = test/txt
out.file.suffix = .java
#package.prefix = com.acme.models

# what to do if output file already exists.
# one of: SKIP, OVERWRITE, RENAME or FAIL
if.exists = OVERWRITE

# algorithms for transforming table and column names to class and field names.
# values are some combination of: CAMEL, CAPITAL, SINGULAR
table.to.model.name = CAMEL,CAPITAL,SINGULAR
column.to.field.name = CAMEL

# default column-to-field type mappings ----------

varchar(255)    String
varchar(1000)   String
numeric(8,2)    Float
timestamp       Date
int             Integer
integer         Integer
int8            Long
bigint          Long
boolean         Boolean
bytea           byte[]
</pre>
</div>
<div class="box-label">
(test/info-test.properties)
</div>
<p>
Many of these properties can be overridden by annotation arguments as describe in the
<a href="#annotations">annotations section</a>.
<p>
You will probably need to add additional column-to-field mappings depending on your
schema.
<p>
One use of the <span class='code'>out.file.suffix</span> property may not be immediately
obvious.  Consider the case where you want to separate the code in your model classes
into basic CRUD methods that are totally machine generated and other methods that are
written by hand you could do this by having each model consist of a base class that
contains the CRUD methods and then the real model class which extends the base class.
You can use SqlMapper to generate each of these types of models by using two
separate config files and make two passes over your schema file.
The config file for generating the base model classes would set the
<span class='code'>if.exists=OVERWRITE</span> property and a
<span class='code'>out.file.suffix=Base.java</span> property. While the config
file for generating the hand written models would have the property
<span class='code'>if.exists=SKIP</span> (or <span class='code'>RENAME</span>)
and <span class='code'>out.file.suffix=.java</span>

<h2>Installation<a name="installation"></a></h2>

SqlMapper has a couple dependencies, namely Java and Ant.  Here are step by
step instructions for installing SqlMapper and its dependencies.
<ol>
<li>Download and un-tar Java (a JDK not just a JRE)</li>
<li>Set the JAVA_HOME environment variable</li>
<li>Add $JAVA_HOME/bin to your PATH
<pre>
   to test type: java -version
   you should see something like: java version "1.6.0_11"
</pre>
</li>
<li>Download and un-tar Ant</li>
<li>Add ant to your PATH
<pre>
   to test type: ant -version
   you should see something like: Apache Ant version 1.7.1 compiled on June 27 2008
</pre>
</li>
<li>Download and un-tar SqlMapper
<pre>
   to test go to the sqlmapper directory that was created and type:

   ant test

   at the end of the output you should see a line that says something like:

   [java] OK (11 tests)
</pre>
</li>
<li>Optionally, you may want to define an environment variable like SQLMAPPER_HOME
which points to the sqlmapper install directory</li>
</ol>

<h2>Use<a name="use"></a></h2>

To use SqlMapper you need to:
<ol>
<li>Annotate the SQL schema file that you would like to map.</li>
<li>Create one or more template files for the model classes you would like to generate.
Feel free to copy and modify one of the templates in the sqlmapper/test/templates directory.
</li>
<li>Create a configuration file and make sure it refers to your annotated SQL file and template
directory.  Again, feel free to start with a copy of one of the properties files in the
sqlmapper/test directory.</li>
<li>invoke sqlmapper via the ant target <em>models</em>, specifying your config file as an argument.
For example:
<pre>
  ant -f $SQLMAPPER_HOME/build.xml models -Dconfig.file=path/to/my_project/sqlmapper.properties
</pre>
Hint: you may want to put your version of this command in a shell script to make it easier to use.
</ol>
</body>
</html>
